import time
import datetime
import smtplib
from email.mime.text import MIMEText
from email.header import Header
import pymysql  # 用于MySQL数据库连接，如需其他数据库可替换

class LogSyncChecker:
    def __init__(self):
        """
        初始化数据库检查器
        :param db_config: 数据库配置字典
        :param email_config: 邮件配置字典
        """
        # 数据库配置
        self.db_config = {
            'host': 'rm-gw8a4drz5t1dde7g3.mysql.germany.rds.aliyuncs.com',
            'user': 'root',
            'password': 'suKeepgo123@cys',
            'database': 'cfx_t',
            'port': 3306,
            'charset': 'utf8mb4'

        }
        
        # 邮件配置 (以QQ邮箱为例，其他邮箱请修改对应SMTP服务器和端口)
        self.email_config = {
            'smtp_server': 'smtp.qq.com',
            'smtp_port': 465,
            'sender': '54269573@qq.com',
            'password': 'tshuovtjejafcafb',
            'receivers': ['chuays@126.com']  # 接收人列表
        }
        self.db_connection = None
        
    def connect_db(self):
        """连接到数据库"""
        try:
            self.db_connection = pymysql.connect(
                host=self.db_config['host'],
                user=self.db_config['user'],
                password=self.db_config['password'],
                database=self.db_config['database'],
                port=self.db_config.get('port', 3306)
            )
            print("数据库连接成功")
            return True
        except Exception as e:
            print(f"数据库连接失败: {str(e)}")
            self.db_connection = None
            return False
    
    def close_db(self):
        """关闭数据库连接"""
        if self.db_connection:
            self.db_connection.close()
            print("数据库连接已关闭")
    
    def check_recent_hours(self, table_name, hour_column='hour', date_column='date'):
        """
        检查最近3小时内是否有数据
        :param table_name: 要查询的表名
        :param hour_column: 小时字段名
        :param date_column: 日期字段名
        :return: 如果存在返回True，否则返回False
        """
        if not self.db_connection:
            print("请先连接数据库")
            return False
            
        try:
            # 获取当前时间和3小时前的时间
            now = datetime.datetime.now()
            three_hours_ago = now - datetime.timedelta(hours=3)
            
            # 获取当前日期和3小时前的日期
            current_date = now.strftime('%Y-%m-%d')
            three_hours_ago_date = three_hours_ago.strftime('%Y-%m-%d')
            
            # 获取当前小时和3小时前的小时
            current_hour = now.hour
            three_hours_ago_hour = three_hours_ago.hour
            
            cursor = self.db_connection.cursor()
            
            # 构建查询：检查最近3小时内是否有数据
            if current_date == three_hours_ago_date:
                # 同一天内，直接检查小时范围
                query = f"""
                SELECT COUNT(*) FROM {table_name}
                WHERE {date_column} = %s
                AND {hour_column} BETWEEN %s AND %s
                """
                cursor.execute(query, (current_date, three_hours_ago_hour, current_hour))
            else:
                # 跨天情况，需要检查两天的数据
                query = f"""
                SELECT COUNT(*) FROM {table_name}
                WHERE ({date_column} = %s AND {hour_column} >= %s)
                OR ({date_column} = %s AND {hour_column} <= %s)
                """
                cursor.execute(query, (
                    three_hours_ago_date, three_hours_ago_hour,
                    current_date, current_hour
                ))
            
            result = cursor.fetchone()
            count = result[0] if result else 0
            
            print(f"最近3小时内找到 {count} 条记录")
            return count > 10
            
        except Exception as e:
            print(f"数据库查询失败: {str(e)}")
            return False
    
    def send_email_notification(self):
        """发送邮件通知"""
        try:
            # 邮件内容
            subject = "数据库hour字段异常通知"
            now = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            content = f"""
            警告：在 {now} 检测到数据库中最近3小时内没有有效数据！
            
            请检查数据采集系统是否正常运行。
            """
            
            # 创建邮件消息
            msg = MIMEText(content, 'plain', 'utf-8')
            msg['Subject'] = Header(subject, 'utf-8')
            msg['From'] = self.email_config['sender']
            msg['To'] = ','.join(self.email_config['receivers'])
            
            # 发送邮件
            with smtplib.SMTP_SSL(self.email_config['smtp_server'], self.email_config['smtp_port']) as server:
                server.login(self.email_config['sender'], self.email_config['password'])
                server.sendmail(
                    self.email_config['sender'],
                    self.email_config['receivers'],
                    msg.as_string()
                )
            
            print("邮件通知发送成功")
            return True
            
        except Exception as e:
            print(f"邮件发送失败: {str(e)}")
            return False
    
    def run_check(self):
        """执行检查流程"""
        try:
            # 连接数据库
            if not self.connect_db():
                return False
            table_name='file_line_counts'
            # 检查最近3小时数据
            has_recent_data = self.check_recent_hours(table_name)
            
            # 如果没有最近数据，发送邮件
            if not has_recent_data:
                print("最近3小时内没有数据，准备发送通知...")
                self.send_email_notification()
            else:
                print("最近3小时内有数据，无需通知")
                
            return True
            
        finally:
            # 确保关闭数据库连接
            self.close_db()

if __name__ == "__main__":
    
    
    # 创建检查器并执行检查
    checker = LogSyncChecker()
    checker.run_check()

